insert overwrite table tag_mid_people_cd
select A.CARDUSER_ID,B.CUSTOMER_ID,B.customer_types,A.customer_level,B.gender,A.MOBILECARTYPE,A.NAME,B.BIRTHDAY,B.RECORDTIME,substr(B.MOBILEPHONE,1,11),B.ADDRESS,C.BALANCE/100
FROM TBL_CARDUSER_cd AS A
JOIN TBL_CUSTOMER_cd AS B
ON A.CUSTOMER_ID = B.CUSTOMER_ID
JOIN TBL_ACCOUNT_cd AS C
ON C.CARDUSER_ID = A.CARDUSER_ID AND C.ACCOUNTTYPE = 2;

insert OVERWRITE table tag_people_cd
select carduser_id,customer_id,customer_type,customer_level,gender,mobile_car_type,name,
case when
birthday between '1940-01-01' and '2000-01-01' then birthday
else null end ,
record_time,mobile_phone,address,balance
from tag_mid_people_cd;

insert overwrite table tag_station_cd
select * from
(
select CARDUSERID,STATIONCODE,COUNT,
row_number() over (partition by CARDUSERID order by COUNT DESC)AS rank from
(SELECT CARDUSERID,STATIONCODE,COUNT(ORDERDETAILS_ID) AS COUNT
FROM TBL_ORDERDETAILS_cd
GROUP BY CARDUSERID,STATIONCODE) a
)b
where b.rank<=1;


insert overwrite table tag_stationb_cd
select CARDUSERID,COUNT(distinct STATIONCODE)
from mid.TBL_ORDERDETAILS_CD 
GROUP BY CARDUSERID ;

insert overwrite table tag_isdiscount_cd
SELECT CARDUSERID,SUM(AMOUNT-REALAMOUNT)/100
FROM TBL_ORDERDETAILS_cd
GROUP BY CARDUSERID;

insert OVERWRITE table tag_oil_cd
select * from
(
select CARDUSERID,OILCODE,
row_number() OVER(partition BY CARDUSERID ORDER BY COUNT DESC)AS rank
from
(select CARDUSERID,OILCODE,COUNT(ORDERDETAILS_ID)AS COUNT
FROM mid.TBL_ORDERDETAILS_CD WHERE LITRE > 0
GROUP BY CARDUSERID,OILCODE)a
)b where b.rank = 1;

insert OVERWRITE table tag_historyDeal_cd
select CARDUSERID,COUNT(ORDERDETAILS_ID),sum(REALAMOUNT)/100
from TBL_ORDERDETAILS_cd
WHERE ORDER_DEAL = 4
GROUP BY CARDUSERID;

insert OVERWRITE table tag_oneMonthOil_cd
select CARDUSERID,count(LITRE)as count,sum(REALAMOUNT)/100 as sum
from TBL_ORDERDETAILS_cd
where SENDTIME > date_sub(CURRENT_DATE,30) and LITRE > 0
GROUP BY CARDUSERID;

insert OVERWRITE table tag_oneMonthNotoil_cd
select CARDUSERID,count(LITRE)as count,sum(REALAMOUNT)/100 as sum
from TBL_ORDERDETAILS_cd
where SENDTIME > date_sub(CURRENT_DATE,30) and LITRE = 0
GROUP BY CARDUSERID;

insert OVERWRITE table tag_threeMonthOil_cd
select CARDUSERID,count(LITRE)as count,sum(REALAMOUNT)/100 as sum
from TBL_ORDERDETAILS_cd
where SENDTIME > date_sub(CURRENT_DATE,90) and LITRE > 0
GROUP BY CARDUSERID;

insert OVERWRITE table tag_threeMonthNotoil_cd
select CARDUSERID,count(LITRE)as count,sum(REALAMOUNT)/100 as sum
from TBL_ORDERDETAILS_cd
where SENDTIME > date_sub(CURRENT_DATE,90) and LITRE = 0
GROUP BY CARDUSERID;

set hive.auto.convert.join=false;

insert OVERWRITE table tag_resulta_cd
select A.carduser_id,A.customer_id,A.customer_type,A.customer_level,A.gender,A.mobile_car_type,A.name,A.birthday,A.record_time,L.lastdeal,A.mobile_phone,A.address,A.balance,B.station_id,C.sum_discount,D.max_mop,E.max_oil,F.max_department,G.oil_num,G.oil_money,H.notoil_num,H.notoil_money,I.oil_num,I.oil_money,J.notoil_num,J.notoil_money,k.deal_num,K.deal_money,M.max
from tag_people_cd as A
left JOIN tag_station_cd as B
on B.carduser_id = A.carduser_id
left join tag_isdiscount_cd as C
on C.carduser_id = A.carduser_id
left join tag_mop as D
on D.carduser_id = A.carduser_id
left join tag_oil_cd as E
on E.carduser_id = A.carduser_id
left join tag_notoilDepartment as F
on F.carduser_id = A.carduser_id
left join tag_oneMonthOil_cd as G
on G.carduser_id = A.carduser_id
left join tag_oneMonthNotoil_cd H
ON H.carduser_id = A.carduser_id
left join tag_threeMonthOil_cd as I 
ON I.carduser_id = A.carduser_id
LEFT JOIN tag_threeMonthNotoil_cd AS J
ON J.carduser_id = A.carduser_id
left join tag_historyDeal_cd as K
on K.carduser_id = A.carduser_id
LEFT JOIN res_vipLastDeal_cd as L
on L.CARDUSERID = A.carduser_id
LEFT JOIN tag_stationb_cd as M 
on M.carduser_id = A.carduser_id;

set hive.auto.convert.join=true;



insert overwrite table tag_resultb_cd
select 
carduser_id,
customer_id,
name,
mobilephone,
maxstationid,
maxmop,
maxoil,
maxdepartment,
case gender 
when '1' then '男' 
when '2' then '女' 
else null end as gender,

case customer_type
when '1' then '潜在顾客'
when '2' then '首次消费顾客'
when '3' then '潜力顾客'
when '4' then '忠诚顾客'
when '5' then '潜在流失顾客'
when '6' then '沉睡顾客'
else null end as customer_type,

case customer_level
when '1' then '普通客户'
when '10' then '金卡客户'
when '20' then '铂金卡客户'
when '30' then '钻石卡客户'
when '40' then '出租车/专快车'
when '50' then '壳牌中国员工'
else null end as customer_level,

case 
when birthday > '1993-01-17' then '青少年'
when birthday < '1993-01-17' and birthday > '1974-01-17' then '青年'
when birthday < '1974-01-17' and birthday > '1958-01-17' then '中年'
when birthday < '1958-01-17' then '老年'
else null  end as birthday,

case 
when recordtime <= date_sub(CURRENT_DATE,7) then '非新注册会员'
else '新注册会员' end as recordtime,

case 
when lastdeal <= 30 then '成长型会员'
when lastdeal > 30 AND lastdeal < 180 then '流失中会员'
else '不活跃会员' end as lastdeal,

case 
when sumdiscount > 50 then '经常使用优惠券型'
when sumdiscount > 0 and sumdiscount <= 50 then '偶尔使用优惠券型'
else '从未使用优惠券型' end as sumdiscount,

case
when onemonthoilnum > 5 then '近期加油频次高'
when onemonthoilnum > 3 and onemonthoilnum <= 5 then '近期加油频次中'
else '近期加油频次低' end as onemonthoilnum,

case 
when onemonthnotoilmoney > 12 then '近期非油消费金额高'
when onemonthnotoilmoney > 5 and onemonthnotoilmoney <= 12 then '近期非油消费金额中'
else '近期非油消费金额低' end as onemonthnotoilmoney,

case 
when threemonthoilnum > 10 then '短期加油频次高'
when threemonthoilnum > 5 and threemonthoilnum <= 10 then '短期加油频次中'
else '短期加油频次低' end as threemonthoilnum

from tag_resulta_cd;

insert overwrite table tag_result_cd
select A.*,concat_ws(' ',
B.gender,
B.customer_type,
B.customer_level,
B.birthday,
B.recordTime,
B.lastdeal,
B.sumDiscount,
B.oneMonthOilNum,
B.onemonthnotoilmoney,
B.threemonthoilnum),null,null from tag_resulta_cd as A
join tag_resultb_cd as B
on A.carduser_id = B.carduser_id;

insert OVERWRITE table tag_frequency_thirty_cd
select CARDUSERID,count(*)
FROM mid.TBL_ORDERDETAILS_CD WHERE ORDER_DEAL = 4 and SENDTIME > date_sub(CURRENT_DATE,30)
GROUP BY CARDUSERID;

insert overwrite table tag_station_thirty_cd
select a.CARDUSERID,concat_ws(' ',collect_set(a.STATIONCODE))   from 
(SELECT CARDUSERID,STATIONCODE
FROM mid.TBL_ORDERDETAILS where SENDTIME > date_sub(CURRENT_DATE,30)
GROUP BY CARDUSERID,STATIONCODE)as a 
GROUP BY A.CARDUSERID;



insert OVERWRITE table tag_oil_thirty_cd
select a.CARDUSERID,concat_ws(' ',collect_set(a.OILCODE)) from 
(select CARDUSERID,OILCODE
FROM mid.TBL_ORDERDETAILS WHERE LITRE > 0 and SENDTIME > date_sub(CURRENT_DATE,30)
GROUP BY CARDUSERID,OILCODE)a
group by a.CARDUSERID;


insert overwrite table tag_resulta_thirty_cd
select a.*,b.station_id,c.max_oil,d.max_department,e.frequency
from tag_station_thirty_cd as b  
left join tag_people_cd as a
on a.carduser_id = b.carduser_id
left join tag_oil_thirty_cd as c 
on c.carduser_id = a.carduser_id
left join tag_notoilDepartment_thirty as d 
on d.carduser_id = a.carduser_id
left join tag_frequency_thirty_cd as e
on a.carduser_id = e.carduser_id;



insert overwrite table tag_resultb_thirty_cd
select 
carduser_id,
customer_id,
name,
mobilephone,
maxstationid,
maxoil,
maxdepartment,
case gender 
when '1' then '男' 
when '2' then '女' 
else null end as gender,

case customer_type
when '1' then '潜在顾客'
when '2' then '首次消费顾客'
when '3' then '潜力顾客'
when '4' then '忠诚顾客'
when '5' then '潜在流失顾客'
when '6' then '沉睡顾客'
else null end as customer_type,

case customer_level
when '1' then '普通客户'
when '10' then '金卡客户'
when '20' then '铂金卡客户'
when '30' then '钻石卡客户'
when '40' then '出租车/专快车'
when '50' then '壳牌中国员工'
else null end as customer_level,

case 
when birthday > '1993-01-17' then '青少年'
when birthday < '1993-01-17' and birthday > '1974-01-17' then '青年'
when birthday < '1974-01-17' and birthday > '1958-01-17' then '中年'
when birthday < '1958-01-17' then '老年'
else null  end as birthday,

case 
when recordtime <= date_sub(CURRENT_DATE,7) then '非新注册会员'
else '新注册会员' end as recordtime
from tag_resulta_thirty_cd;



insert overwrite table tag_result_thirty_cd
select A.*,concat_ws(' ',
B.gender,
B.customer_type,
B.customer_level,
B.birthday,
B.recordTime),null,null from tag_resulta_thirty_cd as A
join tag_resultb_thirty_cd as B
on A.carduser_id = B.carduser_id;

insert OVERWRITE table tag_frequency_sixty_cd
select CARDUSERID,count(*)
FROM mid.TBL_ORDERDETAILS_CD WHERE ORDER_DEAL = 4 and SENDTIME > date_sub(CURRENT_DATE,60)
GROUP BY CARDUSERID;

insert overwrite table tag_station_sixty_cd
select a.CARDUSERID,concat_ws(' ',collect_set(a.STATIONCODE))   from 
(SELECT CARDUSERID,STATIONCODE
FROM mid.TBL_ORDERDETAILS where SENDTIME > date_sub(CURRENT_DATE,60)
GROUP BY CARDUSERID,STATIONCODE)as a 
GROUP BY A.CARDUSERID;



insert OVERWRITE table tag_oil_sixty_cd
select a.CARDUSERID,concat_ws(' ',collect_set(a.OILCODE)) from 
(select CARDUSERID,OILCODE
FROM mid.TBL_ORDERDETAILS WHERE LITRE > 0 and SENDTIME > date_sub(CURRENT_DATE,60)
GROUP BY CARDUSERID,OILCODE)a
group by a.CARDUSERID;



insert overwrite table tag_resulta_sixty_cd
select a.*,b.station_id,c.max_oil,d.max_department,e.frequency
from tag_station_sixty_cd as b  
left join tag_people_cd as a
on a.carduser_id = b.carduser_id
left join tag_oil_sixty_cd as c 
on c.carduser_id = a.carduser_id
left join tag_notoilDepartment_sixty as d 
on d.carduser_id = a.carduser_id
left join tag_frequency_sixty_cd as e
on a.carduser_id = e.carduser_id;



insert overwrite table tag_resultb_sixty_cd
select 
carduser_id,
customer_id,
name,
mobilephone,
maxstationid,
maxoil,
maxdepartment,
case gender 
when '1' then '男' 
when '2' then '女' 
else null end as gender,
case customer_type
when '1' then '潜在顾客'
when '2' then '首次消费顾客'
when '3' then '潜力顾客'
when '4' then '忠诚顾客'
when '5' then '潜在流失顾客'
when '6' then '沉睡顾客'
else null end as customer_type,
case customer_level
when '1' then '普通客户'
when '10' then '金卡客户'
when '20' then '铂金卡客户'
when '30' then '钻石卡客户'
when '40' then '出租车/专快车'
when '50' then '壳牌中国员工'
else null end as customer_level,
case 
when birthday > '1993-01-17' then '青少年'
when birthday < '1993-01-17' and birthday > '1974-01-17' then '青年'
when birthday < '1974-01-17' and birthday > '1958-01-17' then '中年'
when birthday < '1958-01-17' then '老年'
else null  end as birthday,
case 
when recordtime <= date_sub(CURRENT_DATE,7) then '非新注册会员'
else '新注册会员' end as recordtime
from tag_resulta_sixty_cd;



insert overwrite table tag_result_sixty_cd
select A.*,concat_ws(' ',
B.gender,
B.customer_type,
B.customer_level,
B.birthday,
B.recordTime),null,null from tag_resulta_sixty_cd as A
join tag_resultb_sixty_cd as B
on A.carduser_id = B.carduser_id;

insert OVERWRITE table tag_frequency_ninety_cd
select CARDUSERID,count(*)
FROM mid.TBL_ORDERDETAILS_CD WHERE ORDER_DEAL = 4 and SENDTIME > date_sub(CURRENT_DATE,90)
GROUP BY CARDUSERID;

insert overwrite table tag_station_ninety_cd
select a.CARDUSERID,concat_ws(' ',collect_set(a.STATIONCODE))   from 
(SELECT CARDUSERID,STATIONCODE
FROM mid.TBL_ORDERDETAILS where SENDTIME > date_sub(CURRENT_DATE,90)
GROUP BY CARDUSERID,STATIONCODE)as a 
GROUP BY A.CARDUSERID;



insert OVERWRITE table tag_oil_ninety_cd
select a.CARDUSERID,concat_ws(' ',collect_set(a.OILCODE)) from 
(select CARDUSERID,OILCODE
FROM mid.TBL_ORDERDETAILS WHERE LITRE > 0 and SENDTIME > date_sub(CURRENT_DATE,90)
GROUP BY CARDUSERID,OILCODE)a
group by a.CARDUSERID;



insert overwrite table tag_resulta_ninety_cd
select a.*,b.station_id,c.max_oil,d.max_department,e.frequency
from tag_station_ninety_cd as b  
left join tag_people_cd as a
on a.carduser_id = b.carduser_id
left join tag_oil_ninety_cd as c 
on c.carduser_id = a.carduser_id
left join tag_notoilDepartment_ninety as d 
on d.carduser_id = a.carduser_id
left join tag_frequency_ninety_cd as e
on a.carduser_id = e.carduser_id;



insert overwrite table tag_resultb_ninety_cd
select 
carduser_id,
customer_id,
name,
mobilephone,
maxstationid,
maxoil,
maxdepartment,
case gender 
when '1' then '男' 
when '2' then '女' 
else null end as gender,
case customer_type
when '1' then '潜在顾客'
when '2' then '首次消费顾客'
when '3' then '潜力顾客'
when '4' then '忠诚顾客'
when '5' then '潜在流失顾客'
when '6' then '沉睡顾客'
else null end as customer_type,
case customer_level
when '1' then '普通客户'
when '10' then '金卡客户'
when '20' then '铂金卡客户'
when '30' then '钻石卡客户'
when '40' then '出租车/专快车'
when '50' then '壳牌中国员工'
else null end as customer_level,
case 
when birthday > '1993-01-17' then '青少年'
when birthday < '1993-01-17' and birthday > '1974-01-17' then '青年'
when birthday < '1974-01-17' and birthday > '1958-01-17' then '中年'
when birthday < '1958-01-17' then '老年'
else null  end as birthday,
case 
when recordtime <= date_sub(CURRENT_DATE,7) then '非新注册会员'
else '新注册会员' end as recordtime
from tag_resulta_ninety_cd;



insert overwrite table tag_result_ninety_cd
select A.*,concat_ws(' ',
B.gender,
B.customer_type,
B.customer_level,
B.birthday,
B.recordTime),null,null from tag_resulta_ninety_cd as A
join tag_resultb_ninety_cd as B
on A.carduser_id = B.carduser_id;

insert into tag_result_thirty_cd
select a.carduser_id,
a.customer_id,
a.customer_type,
a.customer_level,
a.gender,
a.mobileCarType,
a.name,
a.birthday ,
a.recordTime ,
a.mobilePhone ,
a.address ,
a.balance ,
null ,
null,
null,
null,null,
null,null
from tag_result_cd as a
left outer join tag_result_thirty_cd as b
on a.carduser_id = b.carduser_id
where b.carduser_id is null;

insert into tag_result_sixty_cd
select a.carduser_id,
a.customer_id,
a.customer_type,
a.customer_level,
a.gender,
a.mobileCarType,
a.name,
a.birthday ,
a.recordTime ,
a.mobilePhone ,
a.address ,
a.balance ,
null ,
null,
null,
null,null,
null,null
from tag_result_cd as a
left outer join tag_result_sixty_cd as b
on a.carduser_id = b.carduser_id
where b.carduser_id is null;

insert into tag_result_ninety_cd
select a.carduser_id,
a.customer_id,
a.customer_type,
a.customer_level,
a.gender,
a.mobileCarType,
a.name,
a.birthday ,
a.recordTime ,
a.mobilePhone ,
a.address ,
a.balance ,
null ,
null,
null,
null,null,
null,null
from tag_result_cd as a
left outer join tag_result_ninety_cd as b
on a.carduser_id = b.carduser_id
where b.carduser_id is null;


insert overwrite table tag_station_alltime
select a.CARDUSERID,concat_ws(' ',collect_set(a.STATIONCODE))   from 
(SELECT CARDUSERID,STATIONCODE
FROM TBL_ORDERDETAILS
GROUP BY CARDUSERID,STATIONCODE)as a 
GROUP BY A.CARDUSERID;

insert overwrite table tag_station_alltimecd
select a.CARDUSERID,concat_ws(' ',collect_set(a.STATIONCODE))   from 
(SELECT CARDUSERID,STATIONCODE
FROM TBL_ORDERDETAILS_cd
GROUP BY CARDUSERID,STATIONCODE)as a 
GROUP BY A.CARDUSERID;

insert  OVERWRITE table tag_mop_alltime
select a.carduser_id,concat_ws(' ',collect_set(a.item_description)) from
(select carduser_id,item_description
from transaction_items_mop_vip
group by carduser_id,item_description)as a
GROUP BY A.CARDUSER_ID;

insert OVERWRITE table tag_oil_alltime
select a.CARDUSERID,concat_ws(' ',collect_set(a.OILCODE)) from 
(select CARDUSERID,OILCODE
FROM mid.TBL_ORDERDETAILS WHERE LITRE > 0
GROUP BY CARDUSERID,OILCODE)a
group by a.CARDUSERID;

insert OVERWRITE table tag_oil_alltimecd
select a.CARDUSERID,concat_ws(' ',collect_set(a.OILCODE)) from 
(select CARDUSERID,OILCODE
FROM mid.TBL_ORDERDETAILS_CD WHERE LITRE > 0
GROUP BY CARDUSERID,OILCODE)a
group by a.CARDUSERID;

insert overwrite table tag_notoilDepartment_alltime
select d.carduser_id,concat_ws(' ',collect_set(d.dept_name)) from
(select A.carduser_id as carduser_id,C.dept_name as dept_name
from transaction_items_notoil_vip as A
JOIN mid.products as B
on A.product_code = B.product_code and B.station_id = 50006
join mid.departments as C
on C.department_id = B.department_id and C.station_id=50006
group by A.carduser_id,C.dept_name)d
group by d.carduser_id;

set hive.auto.convert.join=false;

insert OVERWRITE table tag_resulta_alltime
select A.carduser_id,A.customer_id,A.customer_type,A.customer_level,A.gender,A.mobile_car_type,A.name,A.birthday,A.record_time,L.lastdeal,A.mobile_phone,A.address,A.balance,B.station_id,C.sum_discount,D.max_mop,E.max_oil,F.max_department,G.oil_num,G.oil_money,H.notoil_num,H.notoil_money,I.oil_num,I.oil_money,J.notoil_num,J.notoil_money,k.deal_num,K.deal_money,M.max
from tag_people as A
left JOIN tag_station_alltime as B
on B.carduser_id = A.carduser_id
left join tag_isdiscount as C
on C.carduser_id = A.carduser_id
left join tag_mop_alltime as D
on D.carduser_id = A.carduser_id
left join tag_oil_alltime as E
on E.carduser_id = A.carduser_id
left join tag_notoilDepartment_alltime as F
on F.carduser_id = A.carduser_id
left join tag_oneMonthOil as G
on G.carduser_id = A.carduser_id
left join tag_oneMonthNotoil H
ON H.carduser_id = A.carduser_id
left join tag_threeMonthOil as I 
ON I.carduser_id = A.carduser_id
LEFT JOIN tag_threeMonthNotoil AS J
ON J.carduser_id = A.carduser_id
left join tag_historyDeal as K
on K.carduser_id = A.carduser_id
LEFT JOIN res_vipLastDeal as L
on L.CARDUSERID = A.carduser_id
LEFT JOIN tag_stationb as M 
on M.carduser_id = A.carduser_id;

insert overwrite table tag_resultb_alltime
select 
carduser_id,
customer_id,
name,
mobilephone,
maxstationid,
maxmop,
maxoil,
maxdepartment,
case gender 
when '1' then '男' 
when '2' then '女' 
else null end as gender,

case customer_type
when '1' then '潜在顾客'
when '2' then '首次消费顾客'
when '3' then '潜力顾客'
when '4' then '忠诚顾客'
when '5' then '潜在流失顾客'
when '6' then '沉睡顾客'
else null end as customer_type,

case customer_level
when '1' then '普通客户'
when '10' then '金卡客户'
when '20' then '铂金卡客户'
when '30' then '钻石卡客户'
when '40' then '出租车/专快车'
when '50' then '壳牌中国员工'
else null end as customer_level,

case 
when birthday > '1993-01-17' then '青少年'
when birthday < '1993-01-17' and birthday > '1974-01-17' then '青年'
when birthday < '1974-01-17' and birthday > '1958-01-17' then '中年'
when birthday < '1958-01-17' then '老年'
else null  end as birthday,

case 
when recordtime <= date_sub(CURRENT_DATE,7) then '非新注册会员'
else '新注册会员' end as recordtime,

case 
when lastdeal <= 30 then '成长型会员'
when lastdeal > 30 AND lastdeal < 180 then '流失中会员'
else '不活跃会员' end as lastdeal,

case 
when sumdiscount > 50 then '经常使用优惠券型'
when sumdiscount > 0 and sumdiscount <= 50 then '偶尔使用优惠券型'
else '从未适用优惠券型' end as sumdiscount,

case
when onemonthoilnum > 5 then '近期加油频次高'
when onemonthoilnum > 3 and onemonthoilnum <= 5 then '近期加油频次中'
else '近期加油频次低' end as onemonthoilnum,

case 
when onemonthnotoilmoney > 12 then '近期非油消费金额高'
when onemonthnotoilmoney > 5 and onemonthnotoilmoney <= 12 then '近期非油消费金额中'
else '近期非油消费金额低' end as onemonthnotoilmoney,

case 
when threemonthoilnum > 10 then '短期加油频次高'
when threemonthoilnum > 5 and threemonthoilnum <= 10 then '短期加油频次中'
else '短期加油频次低' end as threemonthoilnum

from tag_resulta_alltime;


insert overwrite table tag_result_alltime
select A.*,concat_ws(' ',
B.gender,
B.customer_type,
B.customer_level,
B.birthday,
B.recordTime,
B.lastdeal,
B.sumDiscount,
B.oneMonthOilNum,
B.onemonthnotoilmoney,
B.threemonthoilnum),null,null from tag_resulta_alltime as A
join tag_resultb_alltime as B
on A.carduser_id = B.carduser_id;

set hive.auto.convert.join=false;

insert OVERWRITE table tag_resulta_alltimecd
select A.carduser_id,A.customer_id,A.customer_type,A.customer_level,A.gender,A.mobile_car_type,A.name,A.birthday,A.record_time,L.lastdeal,A.mobile_phone,A.address,A.balance,B.station_id,C.sum_discount,D.max_mop,E.max_oil,F.max_department,G.oil_num,G.oil_money,H.notoil_num,H.notoil_money,I.oil_num,I.oil_money,J.notoil_num,J.notoil_money,k.deal_num,K.deal_money,M.max
from tag_people_cd as A
left JOIN tag_station_alltimecd as B
on B.carduser_id = A.carduser_id
left join tag_isdiscount_cd as C
on C.carduser_id = A.carduser_id
left join tag_mop_alltime as D
on D.carduser_id = A.carduser_id
left join tag_oil_alltimecd as E
on E.carduser_id = A.carduser_id
left join tag_notoilDepartment_alltime as F
on F.carduser_id = A.carduser_id
left join tag_oneMonthOil_cd as G
on G.carduser_id = A.carduser_id
left join tag_oneMonthNotoil_cd H
ON H.carduser_id = A.carduser_id
left join tag_threeMonthOil_cd as I 
ON I.carduser_id = A.carduser_id
LEFT JOIN tag_threeMonthNotoil_cd AS J
ON J.carduser_id = A.carduser_id
left join tag_historyDeal_cd as K
on K.carduser_id = A.carduser_id
LEFT JOIN res_vipLastDeal_cd as L
on L.CARDUSERID = A.carduser_id
LEFT JOIN tag_stationb_cd as M 
on M.carduser_id = A.carduser_id;

insert overwrite table tag_resultb_alltimecd
select 
carduser_id,
customer_id,
name,
mobilephone,
maxstationid,
maxmop,
maxoil,
maxdepartment,
case gender 
when '1' then '男' 
when '2' then '女' 
else null end as gender,

case customer_type
when '1' then '潜在顾客'
when '2' then '首次消费顾客'
when '3' then '潜力顾客'
when '4' then '忠诚顾客'
when '5' then '潜在流失顾客'
when '6' then '沉睡顾客'
else null end as customer_type,

case customer_level
when '1' then '普通客户'
when '10' then '金卡客户'
when '20' then '铂金卡客户'
when '30' then '钻石卡客户'
when '40' then '出租车/专快车'
when '50' then '壳牌中国员工'
else null end as customer_level,

case 
when birthday > '1993-01-17' then '青少年'
when birthday < '1993-01-17' and birthday > '1974-01-17' then '青年'
when birthday < '1974-01-17' and birthday > '1958-01-17' then '中年'
when birthday < '1958-01-17' then '老年'
else null  end as birthday,

case 
when recordtime <= date_sub(CURRENT_DATE,7) then '非新注册会员'
else '新注册会员' end as recordtime,

case 
when lastdeal <= 30 then '成长型会员'
when lastdeal > 30 AND lastdeal < 180 then '流失中会员'
else '不活跃会员' end as lastdeal,

case 
when sumdiscount > 50 then '经常使用优惠券型'
when sumdiscount > 0 and sumdiscount <= 50 then '偶尔使用优惠券型'
else '从未适用优惠券型' end as sumdiscount,

case
when onemonthoilnum > 5 then '近期加油频次高'
when onemonthoilnum > 3 and onemonthoilnum <= 5 then '近期加油频次中'
else '近期加油频次低' end as onemonthoilnum,

case 
when onemonthnotoilmoney > 12 then '近期非油消费金额高'
when onemonthnotoilmoney > 5 and onemonthnotoilmoney <= 12 then '近期非油消费金额中'
else '近期非油消费金额低' end as onemonthnotoilmoney,

case 
when threemonthoilnum > 10 then '短期加油频次高'
when threemonthoilnum > 5 and threemonthoilnum <= 10 then '短期加油频次中'
else '短期加油频次低' end as threemonthoilnum

from tag_resulta_alltimecd;

insert overwrite table tag_result_alltimecd
select A.*,concat_ws(' ',
B.gender,
B.customer_type,
B.customer_level,
B.birthday,
B.recordTime,
B.lastdeal,
B.sumDiscount,
B.oneMonthOilNum,
B.onemonthnotoilmoney,
B.threemonthoilnum),null,null from tag_resulta_alltimecd as A
join tag_resultb_alltimecd as B
on A.carduser_id = B.carduser_id;

insert overwrite table res_openingdate
select SUBSTR(transaction_date,1,10),station_id
from transactions
join (select distinct Y.station_id as station_id2
      from mid.transactions as Z
      join mid.transaction_items_oil as Y
      on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id
      where  Y.item_type!=20 and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)>-24*3600
and unix_timestamp(Z.transaction_date)-unix_timestamp(Y.transaction_date)<24*3600) t_station
      on station_id=station_id2
 where transaction_date >= '2018-01-01'
group by SUBSTR(transaction_date,1,10),station_id;




insert overwrite table tag_result
select a.carduser_id,a.customer_id,a.customer_type,a.customer_level,a.gender,a.mobilecartype,a.name,a.birthday,a.recordtime,a.lastdeal,a.mobilephone,a.address,a.balance,a.maxstationid,a.sumdiscount,a.maxmop,a.maxoil,a.maxdepartment,a.onemonthoilnum,a.onemonthoilmoney,a.onemonthnotoilnum,a.onemonthnotoilmoney,a.threemonthoilnum,a.threemonthoilmoney,a.threemonthnotoilnum,a.threemonthnotoilmoney,a.historydealnum,a.historydealmoney,a.max,a.tag_all,b.rating,b.rating2
 from tag_result as a
left join rfm_result as b on a.carduser_id=b.carduser_id;

insert overwrite table tag_result_cd
select a.carduser_id,a.customer_id,a.customer_type,a.customer_level,a.gender,a.mobilecartype,a.name,a.birthday,a.recordtime,a.lastdeal,a.mobilephone,a.address,a.balance,a.maxstationid,a.sumdiscount,a.maxmop,a.maxoil,a.maxdepartment,a.onemonthoilnum,a.onemonthoilmoney,a.onemonthnotoilnum,a.onemonthnotoilmoney,a.threemonthoilnum,a.threemonthoilmoney,a.threemonthnotoilnum,a.threemonthnotoilmoney,a.historydealnum,a.historydealmoney,a.max,a.tag_all,b.rating,b.rating2
 from tag_result_cd as a
left join rfm_cd_result as b on a.carduser_id=b.carduser_id;

insert overwrite table tag_result_alltime
select a.carduser_id,a.customer_id,a.customer_type,a.customer_level,a.gender,a.mobilecartype,a.name,a.birthday,a.recordtime,a.lastdeal,a.mobilephone,a.address,a.balance,a.maxstationid,a.sumdiscount,a.maxmop,a.maxoil,a.maxdepartment,a.onemonthoilnum,a.onemonthoilmoney,a.onemonthnotoilnum,a.onemonthnotoilmoney,a.threemonthoilnum,a.threemonthoilmoney,a.threemonthnotoilnum,a.threemonthnotoilmoney,a.historydealnum,a.historydealmoney,a.max,a.tag_all,b.rating,b.rating2
 from tag_result_alltime as a
left join rfm_result as b on a.carduser_id=b.carduser_id;

insert overwrite table tag_result_alltimecd
select a.carduser_id,a.customer_id,a.customer_type,a.customer_level,a.gender,a.mobilecartype,a.name,a.birthday,a.recordtime,a.lastdeal,a.mobilephone,a.address,a.balance,a.maxstationid,a.sumdiscount,a.maxmop,a.maxoil,a.maxdepartment,a.onemonthoilnum,a.onemonthoilmoney,a.onemonthnotoilnum,a.onemonthnotoilmoney,a.threemonthoilnum,a.threemonthoilmoney,a.threemonthnotoilnum,a.threemonthnotoilmoney,a.historydealnum,a.historydealmoney,a.max,a.tag_all,b.rating,b.rating2
 from tag_result_alltimecd as a
left join rfm_cd_result as b on a.carduser_id=b.carduser_id;

insert overwrite table tag_result_ninety
select a.carduser_id,a.customer_id,a.customer_type,a.customer_level,a.gender,a.mobilecartype,a.name,a.birthday,a.recordtime,a.mobilephone,a.address,a.balance,a.maxstationid,a.maxoil,a.maxdepartment,a.frequency,a.tag_all,b.rating,b.rating2
 from tag_result_ninety as a
left join rfm_result as b on a.carduser_id=b.carduser_id;

insert overwrite table tag_result_ninety_cd
select a.carduser_id,a.customer_id,a.customer_type,a.customer_level,a.gender,a.mobilecartype,a.name,a.birthday,a.recordtime,a.mobilephone,a.address,a.balance,a.maxstationid,a.maxoil,a.maxdepartment,a.frequency,a.tag_all,b.rating,b.rating2
 from tag_result_ninety_cd as a
left join rfm_cd_result as b on a.carduser_id=b.carduser_id;

insert overwrite table tag_result_sixty
select a.carduser_id,a.customer_id,a.customer_type,a.customer_level,a.gender,a.mobilecartype,a.name,a.birthday,a.recordtime,a.mobilephone,a.address,a.balance,a.maxstationid,a.maxoil,a.maxdepartment,a.frequency,a.tag_all,b.rating,b.rating2
 from tag_result_sixty as a
left join rfm_result as b on a.carduser_id=b.carduser_id;

insert overwrite table tag_result_sixty_cd
select a.carduser_id,a.customer_id,a.customer_type,a.customer_level,a.gender,a.mobilecartype,a.name,a.birthday,a.recordtime,a.mobilephone,a.address,a.balance,a.maxstationid,a.maxoil,a.maxdepartment,a.frequency,a.tag_all,b.rating,b.rating2
 from tag_result_sixty_cd as a
left join rfm_cd_result as b on a.carduser_id=b.carduser_id;

insert overwrite table tag_result_thirty
select a.carduser_id,a.customer_id,a.customer_type,a.customer_level,a.gender,a.mobilecartype,a.name,a.birthday,a.recordtime,a.mobilephone,a.address,a.balance,a.maxstationid,a.maxoil,a.maxdepartment,a.frequency,a.tag_all,b.rating,b.rating2
 from tag_result_thirty as a
left join rfm_result as b on a.carduser_id=b.carduser_id;

insert overwrite table tag_result_thirty_cd
select a.carduser_id,a.customer_id,a.customer_type,a.customer_level,a.gender,a.mobilecartype,a.name,a.birthday,a.recordtime,a.mobilephone,a.address,a.balance,a.maxstationid,a.maxoil,a.maxdepartment,a.frequency,a.tag_all,b.rating,b.rating2
 from tag_result_thirty_cd as a
left join rfm_cd_result as b on a.carduser_id=b.carduser_id;

